﻿/*
 * Post-Deployment Script to populate the partition definition tables for use by the SSAS Partition Manager
 *
 * This should be modified to meet your requirements.  This example is targeted against the Adventure Works cube.
 *
 * See https://SsasPartitionManager.codeplex.com/documentation for full documentation. 
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */

declare @OlapDatabaseName nvarchar(100) = N'AdventureWorksDW2014Multidimensional-EE';
declare @OlapCubeName nvarchar(100) = N'Adventure Works';
declare @TabularDatabaseName nvarchar(100) = N'AW Tabular Model SQL 2014';
declare @TabularCubeName nvarchar(100) = N'Model';



/*
 * Populate the SsasDatabase table using a merge
 */
MERGE spm.SsasDatabase AS T
USING (
	VALUES 
	(@OlapDatabaseName, N'Multidimensional'),
	(@TabularDatabaseName, N'Tabular')
	) AS S(SsasDatabaseName, SsasModelType)
ON
	T.SsasDatabaseName = S.SsasDatabaseName
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasDatabaseName, SsasModelType)
			VALUES	(SsasDatabaseName, SsasModelType);

/*
 * Populate the SsasCube table using a merge
 */
MERGE spm.SsasCube AS T
USING (
	SELECT SsasDatabaseKey, @OlapCubeName as SsasCubeName from spm.SsasDatabase where SsasDatabaseName = @OlapDatabaseName
	union
	SELECT SsasDatabaseKey, @TabularCubeName as SsasCubeName from spm.SsasDatabase where SsasDatabaseName = @TabularDatabaseName
) AS S (SsasDatabaseKey, SsasCubeName)
ON
	T.SsasCubeName = S.SsasCubeName
	and T.SsasDatabaseKey = S.SsasDatabaseKey
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasDatabaseKey, SsasCubeName)
			VALUES	(SsasDatabaseKey, SsasCubeName);

/*
 * Populate the SsasMeasureGroup table using a merge
 */
MERGE spm.SsasMeasureGroup AS T
USING (

	-- Yearly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Customers' as SsasMeasureGroupName, 
		N'Y' AS PartitionPeriod,  
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	-- Quarterly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Sales' as SsasMeasureGroupName, 
		N'Q' AS PartitionPeriod,   
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	-- Monthly partitioning with MDX range in PartitionSlice (i.e. PartitionSliceIsRange = 1) for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Orders' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,  
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,		
		N'20110101' as PartitionStartDate,
		1 as PartitionSliceIsRange  -- we want a range of dates covering the full month e.g {[Date].[Date].&[20130101],[Date].[Date].&[20130102],[Date].[Date].&[20130103],[Date].[Date].&[20130104], etc. 
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Monthly partitioning with MDX member in PartitionSlice (i.e. PartitionSliceIsRange = 0) for Multidimensional
	SELECT SsasCubeKey, 
		N'Reseller Orders' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Weekly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Reseller Sales' as SsasMeasureGroupName, 
		N'W' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Hybrid partitioning where 2014+ is monthly and before 2014 partitioning is quarterly for Multidimensional
	SELECT SsasCubeKey, 
		N'Sales Orders' as SsasMeasureGroupName, 
		N'H' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Yearly partitioning for Tabular
	SELECT SsasCubeKey, 
		N'Reseller Sales' as SsasMeasureGroupName, 
		N'Y' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20090101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @TabularCubeName and SsasDatabaseName = @TabularDatabaseName

UNION ALL

	--  Monthly partitioning for Tabular
	SELECT SsasCubeKey, 
		N'Internet Sales' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20090101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @TabularCubeName and SsasDatabaseName = @TabularDatabaseName
) AS S (SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange)
ON
	T.SsasMeasureGroupName = S.SsasMeasureGroupName
	and T.SsasCubeKey = S.SsasCubeKey
WHEN MATCHED 
THEN	UPDATE 
		SET SsasCubeKey = S.SsasCubeKey, 
		SsasMeasureGroupName = S.SsasMeasureGroupName, 
		PartitionPeriod = S.PartitionPeriod, 
		DateColumnName = S.DateColumnName, 
		DateColumnDataType = S.DateColumnDataType, 
		PartitionStartDate = S.PartitionStartDate,
		PartitionSliceIsRange = S.PartitionSliceIsRange
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange)
			VALUES	(SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange);

GO

-- Populate the server list
MERGE spm.SsasServer AS T
USING (
	VALUES 
	(N'UAT', N'yourUATServer'),
	(N'PROD', N'yourPRODServer'),
	(N'DEV', N'localhost'),
	(N'DEV_TAB', N'localhost\ast')
	) AS S (SsasServerType, SsasServerName)
ON
	T.SsasServerName = S.SsasServerName
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasServerType, SsasServerName)
			VALUES	(SsasServerType, SsasServerName);
GO


